<?php
declare (strict_types = 1);

namespace app\command\logic;

use app\command\logic\BaseLogic;
use think\console\Output;

/**
 * 统计-商品数据
 * php think cli stats_goods --msg='{"days":30}'
 */
class CliStatsGoodsLogic extends BaseLogic implements CliInterface
{

    /**
     * 入口方法
     * {@inheritdoc}
     */
    public function run(Output $output, array $msg): bool
    {
        $insertData = [];
        $date       = isset($msg['date']) ? ($msg['date'] == 'today' ? date('Y-m-d') : $msg['date']) : null;
        if ( ! empty($date)) {
            $insertData[] = self::dataForGoods($date);
            self::dataForGoodsSpu($date);
        } else {
            $days = intval($msg['days'] ?? 1);
            for ($i = 1; $i <= $days; $i++) {
                $date = date('Y-m-d', strtotime("-$i days"));
                // echo $date . "\n";exit;
                $insertData[] = self::dataForGoods($date);

                self::dataForGoodsSpu($date);
            }
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_goods')
                ->replace()
                ->insertAll($insertData);
        }
        return true;
    }

    /**
     * 统计商品数据排行
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoodsSpu($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'goods_id',
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods',
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods',
        ];
        $items = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->where('deleted', 0)
            ->group('goods_id')
            ->select()->toArray();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // echo "\n";
        // echo json_encode($items, 320);exit;
        if (empty($items)) {
            return [];
        }
        $goods_ids = array_column($items, 'goods_id');
        // var_dump($goods_ids);exit;
        $goodsMap = \think\facade\Db::table('goods')
            ->whereIn('id', $goods_ids)
            ->column('id,goods_title', 'id');
        // echo json_encode($goodsMap, 320);exit;

        $goodsMap2 = \think\facade\Db::table('supply_goods')
            ->whereIn('id', $goods_ids)
            ->column('id,name goods_title', 'id');
        // echo json_encode($goodsMap2, 320);exit;
        $insertDataSpu = [];
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        foreach ($items as $key => $data) {
            $g = $goodsMap[$data['goods_id']] ?? [];
            if (empty($g)) {
                $g = $goodsMap2[$data['goods_id']] ?? [];
            }
            // var_dump($g);
            // echo "\n";
            if (empty($g)) {
                continue;
            }
            $data['goods_title'] = $g['goods_title'];
            // $data['view_count']  = $g['view_count'];

            $data['view_count'] = \think\facade\Db::table('member_oplog')
                ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            // ->where('api', '/api/goods/goodsDetail')
            // ->where('params', 'like', '%"' . $data['goods_id'] . '"%')
                ->where('params', 'like', '%{"goodsId":"' . $data['goods_id'] . '"%')
                ->whereBetweenTime('create_at', $date, $end)
                ->count();
            $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)

            // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;
            $data['add_cart'] = \think\facade\Db::table('member_cart')
                ->where('deleted', 0)
                ->where('goods_id', $data['goods_id'])
                ->whereBetweenTime('create_at', $date, $end)
                ->sum('quantity');

            $data['date'] = $date;

            $insertDataSpu[] = $data;
        }

        \think\facade\Db::table('stats_goods_spu')
            ->replace()
            ->insertAll($insertDataSpu);

        return $data;
    }

    /**
     * 统计商品数据
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoods($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods',
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods',
        ];
        $data = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        $data['date'] = $date;
        // use index idx_Status_Deleted_Type_Category_Sales
        $data['sale_goods'] = \think\facade\Db::table('goods')
            ->where('deleted', 0)
            ->where('status', 1) // 0审核中 1上架 2下架
            ->where('create_at', '<', $end)
            ->count();
        $data['sale_goods'] += \think\facade\Db::table('supply_goods')
            ->where('deleted', 0)
            ->where('status', 0) // 商品状态：0、已上架1、已下架'
        // ->where('create_at', '<', $end)
            ->count();
        // echo \think\facade\Db::table('goods')->getlastsql();exit;
        // use index idx_Api_CreateAt
        $data['view_count'] = \think\facade\Db::table('member_oplog')
            ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

        $data['add_cart'] = \think\facade\Db::table('member_cart')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('quantity');

        // echo \think\facade\Db::table('order_goods')->getlastsql();exit;
        $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)
        return $data;
    }

}
